<?php
//date_default_timezone_set('Asia/Shanghai');

/**
* 
*/
class beforeVerify
{
    
    /**
     * 每次touch的数量
     */
    public static $touchNum = 300;

    /**
     * 触摸相同单子的间隔时间
     */
    public static $time_refresh = 3600;

    /**
     * 选择不同环境的数据库
     */
    public static $dbtype = 'products';

    /**
     * 数据库配置文件
     */
    public static $dbConfig = [
        
        'dev' => [
            // 测试环境
            'beforeVerify' => [
                'driver' => 'mysql',
                'host' => '118.190.137.64',
                'port' => 3306,
                'database' => 'qutiqian',
                'username' => 'root',
                'password' => 'MhxzKhl#',
                'charset' => 'utf8',
                'collation' => 'utf8_general_ci',
                'prefix' => '',
                'timezone' => '+00:00',
                'strict' => false
            ]
        ],
        'products' => [
            // 测试环境
            'beforeVerify' => [
                'driver' => 'mysql',
                'host' => 'rm-m5e3chzm2kyq1d12l.mysql.rds.aliyuncs.com',
                'port' => 3306,
                'database' => 'zhaoqianhua',
                'username' => 'webqu',
                'password' => 'MhxzKhl#',
                'charset' => 'utf8',
                'collation' => 'utf8_general_ci',
                'prefix' => '',
                'timezone' => '+00:00',
                'strict' => false
            ]
        ]
    ]
    ;

    /**
     * 校验4-21订单MYSQL数据库连接
     */
    private static $beforeVerifyMysql = null;

    /**
     * 4-21脚本校验日志文件
     */
    private $beforeVerifyLogFile = '/data/wwwroot/shell/beforeVerify_log.txt';


    /**
     * 获取4-21的带校验数据库的连接
     *
     * @return null
     */
    public static function getBeforeVerifyMysqlDBInstance()
    {
        if (is_null(self::$beforeVerifyMysql)) {
            $db = self::$dbConfig[self::$dbtype]['beforeVerify']['database'];
            $host = self::$dbConfig[self::$dbtype]['beforeVerify']['host'];
            $username = self::$dbConfig[self::$dbtype]['beforeVerify']['username'];
            $password = self::$dbConfig[self::$dbtype]['beforeVerify']['password'];
            $port = self::$dbConfig[self::$dbtype]['beforeVerify']['port'];
            self::$beforeVerifyMysql = new \PDO("mysql:dbname=$db;host=$host;port=$port", $username, $password);
        }
        return self::$beforeVerifyMysql;
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        if (isset($_SERVER['argv'][1]) && ($_SERVER['argv'][1] == 'rong')) {
            $this->separateRongEffectiveCheck();
            $this->writeFile('融360用户跑脚本'. date("Y-m-d H:i:s"), $this->beforeVerifyLogFile);
        } elseif (isset($_SERVER['argv'][1]) && ($_SERVER['argv'][1] == 'DKQB')) {
            $this->separateDKQBEffectiveCheck();
            $this->writeFile('贷款钱包用户跑脚本'. date("Y-m-d H:i:s"), $this->beforeVerifyLogFile);
        } elseif (isset($_SERVER['argv'][1]) && ($_SERVER['argv'][1] == 'mine')) {
            $this->separateMineEffectiveCheck();
            $this->writeFile('找钱花用户跑脚本'. date("Y-m-d H:i:s"), $this->beforeVerifyLogFile);
        } else {
            $this->beforeVerifyInfo();
            $this->writeFile('默认各渠道用户跑脚本'. date("Y-m-d H:i:s"), $this->beforeVerifyLogFile);
        }
    }

    public function beforeVerifyInfo()
    {
        $connection = self::getBeforeVerifyMysqlDBInstance();
        //上来先取100条touch
        $customersAll = [];
        $consumesAll = [];
        $touch_time_refresh = time() - self::$time_refresh;
        $sqlsql = "SELECT id,customer_id FROM consume_history WHERE type = 4 AND status = 21 AND touch_time < ". $touch_time_refresh ." LIMIT ". self::$touchNum ;
        $stmt = $connection->prepare($sqlsql);
        $stmt->execute([]);
        $rowrow = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        foreach ($rowrow as $key => $value) {
            $customersAll[] = $value['customer_id'];
            $consumesAll[] = $value['id'];
        }

        $consumesStr = join(",",$consumesAll);
        $str = "beforeVerifyInfo|记录touch时间|". strtotime(date("Y-m-d H:i:s")) ."|触及consume_ids|". $consumesStr ."|";
        $this->writeFile($str, $this->beforeVerifyLogFile);
        $time = strtotime(date("Y-m-d H:0:0"));
        $sql_touch = "UPDATE consume_history SET touch_time = $time WHERE id IN ($consumesStr)";
        $stmt = $connection->prepare($sql_touch);
        $stmt->execute([]);


        $customersAllStr = join(",",$customersAll);



        //对混合查询的customer筛选Third
        $customersThird = [];
        $sqlsql = "SELECT id customer_id FROM customer 
                WHERE user_resource != 0 
                    AND customer_id in ($customersAllStr) ";
        $stmt = $connection->prepare($sqlsql);
        $stmt->execute([]);
        $row1 = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        foreach ($row1 as $key => $value) {
            $customersThird[] = $value['customer_id'];
        }
        $customersThirdWaitCheckArr = array_unique($customersThird);
        $customersThirdWaitCheckStr = join(",",$customersThirdWaitCheckArr);

        //对混合查询的customer筛选Mine
        $customersMine = [];
        $sqlsql = "SELECT id customer_id FROM customer 
                WHERE user_resource = 0 
                    AND customer_id in ($customersAllStr) ";
        $stmt = $connection->prepare($sqlsql);
        $stmt->execute([]);
        $rowrow = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        foreach ($rowrow as $key => $value) {
            $customersMine[] = $value['customer_id'];
        }
        $customersMineWaitCheckArr = array_unique($customersMine);
        $customersMineWaitCheckStr = join(",",$customersMineWaitCheckArr);










        $customersArr = $this->pictureThirdEffectiveCheck($connection,$customersThirdWaitCheckStr);
        $customersDiff = array_diff($customersAll, $customersArr);

        
        $customersArr = $this->pictureMineEffectiveCheck($connection,$customersMineWaitCheckStr);
        $customersDiff = array_diff($customersDiff, $customersArr);

        $customersDiff = array_unique($customersDiff);
        $customersStr = join(",",$customersDiff);


        $this->rongEffectiveCheck($connection, $customersStr);

        // $this->DKQBEffectiveCheck($connection, $customersStr);

        $this->mineEffectiveCheck($connection, $customersStr);

        
        // while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
        //     if (!empty($row['customer_id']) && !empty($row['sesame_score']) && !empty($row['id_num']) && !empty($row['face_pass']) && !empty($row['china_mobile'])) {

        //      $this->separateComeVerify($connection,$row['customer_id'],$row['consume_id']);
        //     } else {
        //         $customer_id = $row['customer_id'];
        //         $sesame_score = $row['sesame_score'];
        //         $kexin_score = $row['kexin_score'];
        //         $id_num = $row['id_num'];
        //         $face_pass = $row['face_pass'];
        //         $china_mobile = $row['china_mobile'];

        //         $str = "未更改4-23状态原因|customer_id|$customer_id|sesame_score|$sesame_score|id_num|$id_num|face_pass|$face_pass|china_mobile|$china_mobile|"
        //         $this->writeFile($str, $this->beforeVerifyLogFile);
        //     }
            
        // }
    }

    //单独的融360客户检查、可单独使用
    public function separateRongEffectiveCheck()
    {
        $connection = self::getBeforeVerifyMysqlDBInstance();
        //上来先取100条touch
        $customersAll = [];
        $consumesAll = [];
        $touch_time_refresh = time() - self::$time_refresh;
        $sqlsql = "SELECT id,customer_id FROM consume_history WHERE type = 4 AND status = 21 AND laiyuan = 1 AND touch_time < ". $touch_time_refresh ." LIMIT ". self::$touchNum ;
        $stmt = $connection->prepare($sqlsql);
        $stmt->execute([]);
        $rowrow = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        foreach ($rowrow as $key => $value) {
            $customersAll[] = $value['customer_id'];
            $consumesAll[] = $value['id'];
        }

        $consumesStr = join(",",$consumesAll);
        $str = "separateRongEffectiveCheck|记录touch时间|". strtotime(date("Y-m-d H:i:s")) ."|触及consume_ids|". $consumesStr ."|";
        $this->writeFile($str, $this->beforeVerifyLogFile);
        $time = strtotime(date("Y-m-d H:0:0"));
        $sql_touch = "UPDATE consume_history SET touch_time = $time WHERE id IN ($consumesStr)";
        $stmt = $connection->prepare($sql_touch);
        $stmt->execute([]);

        $customersThirdWaitCheckStr = join(",",$customersAll);

        $customersArr = $this->pictureThirdEffectiveCheck($connection,$customersThirdWaitCheckStr);

        $customersDiff = array_diff($customersAll, $customersArr);
        $customersDiff = array_unique($customersDiff);
        $customersStr = join(",",$customersDiff);

        $this->rongEffectiveCheck($connection, $customersStr);
    }

    //单独的贷款钱包客户检查、可单独使用
    public function separateDKQBEffectiveCheck()
    {
        $connection = self::getBeforeVerifyMysqlDBInstance();
        //上来先取100条touch
        $customersAll = [];
        $consumesAll = [];
        $touch_time_refresh = time() - self::$time_refresh;
        $sqlsql = "SELECT id,customer_id FROM consume_history WHERE type = 4 AND status = 21 AND laiyuan = 2 AND touch_time < ". $touch_time_refresh ." LIMIT ". self::$touchNum ;
        $stmt = $connection->prepare($sqlsql);
        $stmt->execute([]);
        $rowrow = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        foreach ($rowrow as $key => $value) {
            $customersAll[] = $value['customer_id'];
            $consumesAll[] = $value['id'];
        }

        $consumesStr = join(",",$consumesAll);
        $str = "separateDKQBEffectiveCheck|记录touch时间|". strtotime(date("Y-m-d H:i:s")) ."|触及consume_ids|". $consumesStr ."|";
        $this->writeFile($str, $this->beforeVerifyLogFile);
        $time = strtotime(date("Y-m-d H:0:0"));
        $sql_touch = "UPDATE consume_history SET touch_time = $time WHERE id IN ($consumesStr)";
        $stmt = $connection->prepare($sql_touch);
        $stmt->execute([]);

        $customersThirdWaitCheckStr = join(",",$customersAll);

        $customersArr = $this->pictureThirdEffectiveCheck($connection,$customersThirdWaitCheckStr);

        $customersDiff = array_diff($customersAll, $customersArr);
        $customersDiff = array_unique($customersDiff);
        $customersStr = join(",",$customersDiff);

        $this->DKQBEffectiveCheck($connection, $customersStr);
    }

    //单独的找钱花客户检查、可单独使用
    public function separateMineEffectiveCheck()
    {
        $connection = self::getBeforeVerifyMysqlDBInstance();
        //上来先取100条touch
        $customersAll = [];
        $consumesAll = [];
        $touch_time_refresh = time() - self::$time_refresh;
        $sqlsql = "SELECT id,customer_id FROM consume_history WHERE type = 4 AND status = 21 AND laiyuan = 0 AND touch_time < ". $touch_time_refresh ." LIMIT ". self::$touchNum ;
        $stmt = $connection->prepare($sqlsql);
        $stmt->execute([]);
        $rowrow = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        foreach ($rowrow as $key => $value) {
            $customersAll[] = $value['customer_id'];
            $consumesAll[] = $value['id'];
        }

        $consumesStr = join(",",$consumesAll);
        $str = "separateMineEffectiveCheck|记录touch时间|". strtotime(date("Y-m-d H:i:s")) ."|触及consume_ids|". $consumesStr ."|";
        $this->writeFile($str, $this->beforeVerifyLogFile);
        $time = strtotime(date("Y-m-d H:0:0"));
        $sql_touch = "UPDATE consume_history SET touch_time = $time WHERE id IN ($consumesStr)";
        $stmt = $connection->prepare($sql_touch);
        $stmt->execute([]);


        $customersMineWaitCheckStr = join(",",$customersAll);


        $customersArr = $this->pictureMineEffectiveCheck($connection,$customersMineWaitCheckStr);

        $customersDiff = array_diff($customersAll, $customersArr);
        $customersDiff = array_unique($customersDiff);
        $customersStr = join(",",$customersDiff);

        $this->mineEffectiveCheck($connection, $customersStr);
    }


    /**
     * Create a new command instance.
     */
    public function __construct()
    {}

    public function pictureThirdEffectiveCheck($connection,$customersThirdWaitCheckStr)
    {
        $customersArr = [];
        $sql1 = "SELECT COUNT(DISTINCT type) type_count,customer_id FROM `thirdparty_upload_file` WHERE customer_id in ($customersThirdWaitCheckStr) GROUP BY customer_id HAVING COUNT(DISTINCT type) != 3 OR COUNT(type = 25) < 4";
        $stmt = $connection->prepare($sql1);
        $stmt->execute([]);
        $row1 = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        foreach ($row1 as $key => $value) {
            $customersArr[] = $value['customer_id'];
        }

        $customersArr = array_unique($customersArr);
        return $customersArr;
    }

    public function pictureMineEffectiveCheck($connection,$customersMineWaitCheckStr)
    {
        $customersArr = [];
        $sql1 = "SELECT COUNT(DISTINCT type) type_count,customer_id FROM `upload_file` WHERE customer_id in ($customersMineWaitCheckStr) GROUP BY customer_id HAVING COUNT(DISTINCT type) != 3 OR COUNT(type = 25) < 4";
        $stmt = $connection->prepare($sql1);
        $stmt->execute([]);
        $row1 = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        foreach ($row1 as $key => $value) {
            $customersArr[] = $value['customer_id'];
        }

        $customersArr = array_unique($customersArr);
        return $customersArr;
    }

    public function rongEffectiveCheck($connection, $customersStr)
    {
        $sql = "SELECT DISTINCT(ch.id) as consume_id 
                FROM consume_history as ch 
                    left join customer as c 
                        on ch.customer_id = c.id 
                    left join customer_auth as ca 
                        on ch.customer_id = ca.customer_id 
                    left join customer_bank_info as cbi 
                        on ch.customer_id = cbi.customer_id 
                    left join thirdparty_data as td 
                        on ch.customer_id = td.customer_id 
                    -- left join thirdparty_call_log as tcl 
                    --     on ch.customer_id = tcl.customer_id 
                    left join thirdparty_flow as tf 
                        on ch.customer_id = tf.customer_id 
                    left join customer_contact as cc 
                        on ch.customer_id = cc.customer_id 
                    left join customer_extra_info as cei 
                        on ch.customer_id = cei.customer_id 
                WHERE c.sesame_score <> 0 
                    AND c.mobile <> 0 
                    AND c.real_name is not null 
                    AND c.id_num is not null 
                    AND c.device_id is not null 
                    AND c.birthplace != '' 
                    AND c.valid_period != '' 
                    AND ca.id_num <> 0 
                    AND ca.face_pass <> 0 
                    AND ca.china_mobile <> 0 
                    AND cbi.card_num is not null 
                    AND cbi.reserved_mobile is not null 
                    AND cbi.gate_id is not null 
                    AND td.mobile_data is not null 
                    AND tf.rescouce = 1 -- 这里是融360的用户标识
                    AND cei.wechat is not null 
                    AND cei.qq_num is not null -- 永山说这里不是必填的是否校验待确认
                    AND cei.user_address is not null 
                    AND cei.company_name is not null -- 永山说这里不是必填的是否校验待确认
                    AND cei.company_address is not null -- 永山说这里不是必填的是否校验待确认
                    AND cei.brothers_name is not null 
                    AND cei.brothers_phone is not null 
                    AND cei.friends_name is not null 
                    AND cei.friends_phone is not null 
                    -- AND tcl.customer_id is not null 这里通话记录没有要查的条件标识
                    AND ch.type = 4 
                    AND ch.status = 21 
                    AND ch.consume_amount is not null 
                    AND ch.repay_id is not null 
                    AND ch.customer_id in ($customersStr)";
        $stmt = $connection->prepare($sql);
        $stmt->execute([]);
        $row = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        $consumesEffectiveArr = [];
        foreach ($row as $key => $value) {
            $consumesEffectiveArr[] = $value['consume_id'];
        }
        $consumesEffectiveArr = array_unique($consumesEffectiveArr);
        $consumesEffectiveStr = join(",",$consumesEffectiveArr);
        $sql_set = "UPDATE consume_history SET status = 23 WHERE id IN ($consumesEffectiveStr)";
        $stmt = $connection->prepare($sql_set);
        $ret = $stmt->execute([]);//true入库成功false失败
        if ($ret || $ret == 0) {
            $str = "批量更改4-23状态成功|融360用户|sql_set|". $sql_set ."|ret|". $ret ."|";
            $this->writeFile($str, $this->beforeVerifyLogFile);
        } else {
            $str = "批量更改4-23状态失败|融360用户|sql_set|". $sql_set ."|ret|". $ret ."|";
            $this->writeFile($str, $this->beforeVerifyLogFile);
        }
    }

    public function DKQBEffectiveCheck($connection, $customersStr)
    {
        $sql = "SELECT DISTINCT(ch.id) as consume_id 
                FROM consume_history as ch 
                    left join customer as c 
                        on ch.customer_id = c.id 
                    left join customer_auth as ca 
                        on ch.customer_id = ca.customer_id 
                    left join customer_bank_info as cbi 
                        on ch.customer_id = cbi.customer_id 
                    left join thirdparty_data as td 
                        on ch.customer_id = td.customer_id 
                    left join thirdparty_call_log as tcl 
                        on ch.customer_id = tcl.customer_id 
                    left join thirdparty_flow as tf 
                        on ch.customer_id = tf.customer_id 
                    left join customer_contact as cc 
                        on ch.customer_id = cc.customer_id 
                    left join customer_extra_info as cei 
                        on ch.customer_id = cei.customer_id 
                WHERE c.sesame_score <> 0 
                    AND c.mobile <> 0 
                    AND c.real_name <> 0 
                    AND c.id_num <> 0 
                    AND c.device_id is not null 
                    AND ca.id_num <> 0 
                    AND ca.face_pass <> 0 
                    AND ca.china_mobile <> 0 
                    AND cbi.card_num is not null 
                    AND cbi.reserved_mobile is not null 
                    AND cbi.gate_id is not null 
                    AND td.mobile_contract is not null
                    AND tf.rescouce = 2 -- 这里是贷款钱包的用户标识
                    AND cei.qq_num is not null 
                    AND cei.extra_info_json is not null 
                    AND cei.user_address is not null 
                    AND cei.company_name is not null 
                    AND cei.company_address is not null 
                    -- AND tcl.customer_id is not null 这里通话记录没有要查的条件标识
                    AND ch.type = 4 
                    AND ch.status = 21 
                    AND ch.consume_amount is not null 
                    AND ch.repay_id is not null 
                    AND ch.customer_id in ($customersStr)";
        $stmt = $connection->prepare($sql);
        $stmt->execute([]);
        $row = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        $consumesEffectiveArr = [];
        foreach ($row as $key => $value) {
            $consumesEffectiveArr[] = $value['consume_id'];
        }
        $consumesEffectiveArr = array_unique($consumesEffectiveArr);
        $consumesEffectiveStr = join(",",$consumesEffectiveArr);
        $sql_set = "UPDATE consume_history SET status = 23 WHERE id IN ($consumesEffectiveStr)";
        $stmt = $connection->prepare($sql_set);
        $ret = $stmt->execute([]);//true入库成功false失败
        if ($ret || $ret == 0) {
            $str = "批量更改4-23状态成功|贷款钱包|sql_set|". $sql_set ."|ret|". $ret ."|";
            $this->writeFile($str, $this->beforeVerifyLogFile);
        } else {
            $str = "批量更改4-23状态失败|贷款钱包|sql_set|". $sql_set ."|ret|". $ret ."|";
            $this->writeFile($str, $this->beforeVerifyLogFile);
        }
    }

    public function mineEffectiveCheck($connection, $customersStr)
    {
        $sql = "SELECT DISTINCT(ch.id) as consume_id 
                FROM consume_history as ch 
                    left join customer as c 
                        on ch.customer_id = c.id 
                    left join customer_auth as ca 
                        on ch.customer_id = ca.customer_id 
                    left join customer_bank_info as cbi 
                        on ch.customer_id = cbi.customer_id  
                    left join certificate_data as cd 
                        on c.mobile = cd.mobile 
                    left join customer_contact as cc 
                        on ch.customer_id = cc.customer_id 
                    left join customer_extra_info as cei 
                        on ch.customer_id = cei.customer_id 
                WHERE c.sesame_score <> 0 
                    AND c.mobile <> 0 
                    AND c.real_name is not null 
                    AND c.id_num is not null 
                    AND c.device_id is not null 
                    AND c.birthplace != '' 
                    AND c.valid_period != '' 
                    AND ca.id_num <> 0 
                    AND ca.face_pass <> 0 
                    AND ca.china_mobile <> 0 
                    AND cbi.card_num is not null 
                    AND cbi.reserved_mobile is not null 
                    AND cbi.gate_id is not null 
                    -- AND tf.rescouce = 2 -- 这里是找钱花的用户标识
                    AND cei.wechat is not null 
                    AND cei.qq_num is not null 
                    AND cei.extra_info_json is not null 
                    AND cei.user_address is not null 
                    AND cei.company_name is not null 
                    AND cei.company_address is not null 
                    -- AND tcl.customer_id is not null 这里通话记录没有要查的条件标识
                    AND ch.type = 4 
                    AND ch.status = 21 
                    AND ch.consume_amount is not null 
                    AND ch.repay_id is not null 
                    AND ch.customer_id in ($customersStr)";
        $stmt = $connection->prepare($sql);
        $stmt->execute([]);
        $row = $stmt->fetchAll(\PDO::FETCH_ASSOC);
        $consumesEffectiveArr = [];
        foreach ($row as $key => $value) {
            $consumesEffectiveArr[] = $value['consume_id'];
        }
        $consumesEffectiveArr = array_unique($consumesEffectiveArr);
        $consumesEffectiveStr = join(",",$consumesEffectiveArr);
        $sql_set = "UPDATE consume_history SET status = 23 WHERE id IN ($consumesEffectiveStr)";
        $stmt = $connection->prepare($sql_set);
        $ret = $stmt->execute([]);//true入库成功false失败
        if ($ret || $ret == 0) {
            $str = "批量更改4-23状态成功|找钱花|sql_set|". $sql_set ."|ret|". $ret ."|";
            $this->writeFile($str, $this->beforeVerifyLogFile);
        } else {
            $str = "批量更改4-23状态失败|找钱花|sql_set|". $sql_set ."|ret|". $ret ."|";
            $this->writeFile($str, $this->beforeVerifyLogFile);
        }
    }

    public function writeFile($msg, $file,$noenter=false)
    {
        if(!$noenter){
            $msg .= "\n";
        }
        $handle = fopen($file, "a+");
        fwrite($handle, $msg);
        fclose($handle);
    }

}

$scriptIndex = new beforeVerify();
$scriptIndex->handle();
